package com.genreate.util.xinyuan;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.extra.qrcode.QrCodeUtil;
import cn.hutool.extra.qrcode.QrConfig;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONUtil;
import com.genreate.util.dbUtil.DbUtil;
import com.genreate.util.dbUtil.JdbcConfig;

import java.io.File;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class XyTables {
    public static void main(String[] args) {
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
        List<Entity> query = DbUtil.querys("select * from car_reservation_form");
        for (Entity e : query) {
            Date create_time = e.getDate("create_time");
            Integer order_sno = e.getInt("order_sno");
            Long id = e.getLong("id");
            String yyyyMMdd = DateUtil.format(create_time, "yyyyMMdd");
            String format = StrUtil.format("SJ{}{}", yyyyMMdd, StrUtil.padPre(order_sno.toString(), 5, "0"));
            String update = StrUtil.format("update car_reservation_form set order_no = '{}' where id = {};", format, id);
            DbUtil.execute(update);
        }

    }

    private static void carXz() {
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
        List<Entity> query = DbUtil.querys("select * from car_option_data");
        for (Entity e : query) {
            Long car_option_id = e.getLong("car_option_id");
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanCloud());
            //获取海豹图片
            List<Entity> fileList = DbUtil.querys("select * from upload_file where data_id = ? and file_source in (11)",car_option_id);
            //选装图片
            String xz = fileList.stream().filter(x-> 11 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            String update = StrUtil.format("update car_option_data set car_option_img = '{}' where car_option_id = {};",
                    xz, car_option_id);
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
            DbUtil.execute(update);
        }
    }

    private static void carColor() {
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
        List<Entity> query = DbUtil.querys("select * from app_car_color");
        for (Entity e : query) {
            Long color_id = e.getLong("color_id");
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanCloud());
            //获取海豹图片
            List<Entity> fileList = DbUtil.querys("select * from upload_file where data_id = ? and file_source in (10)",color_id);
           //颜色图片
            String ys = fileList.stream().filter(x-> 10 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            String update = StrUtil.format("update app_car_color set car_color_img = '{}' where color_id = {};",
                    ys, color_id);
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
            DbUtil.execute(update);
        }
    }

    private static void poster() {
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
        List<Entity> query = DbUtil.querys("select * from app_car_poster where poster_id in (1,2,3,4,5,7)");
        for (Entity e : query) {
            Long posterId = e.getLong("poster_id");
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanCloud());
            //获取海豹图片
            List<Entity> fileList = DbUtil.querys("select * from upload_file where data_id = ? and file_source in (1,2,3,4,5)",posterId);
            //购车图片
            String gc = fileList.stream().filter(x-> 1 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            //车辆详情图片
            String xq = fileList.stream().filter(x-> 2 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            //车辆参数图片
            String cs = fileList.stream().filter(x-> 3 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            //亮点图片
            String ld = fileList.stream().filter(x-> 4 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            //权益图片
            String qy = fileList.stream().filter(x-> 5 == x.getInt("file_source")).map(x->x.getStr("file_path")).collect(Collectors.joining(","));
            String update = StrUtil.format("update app_car_poster set car_purchase_img = '{}',car_info_img = '{}'," +
                            "car_parameters_img = '{}',car_light_img = '{}',car_rights_img = '{}' where poster_id = {};",
                    gc, xq, cs, ld, qy, posterId);
            DbUtil.setJdbcConfig(JdbcConfig.xinyuanManage());
            DbUtil.execute(update);
        }
    }

    private static void jxTbGn() {
        String sql1 = "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;";
        String sql2 = "START SLAVE;";
        String sql3 = "SHOW SLAVE STATUS;";
        DbUtil.setJdbcConfig(JdbcConfig.xy_overseas_shop_testid());
        for (int i = 0; i < 300; i++) {
            int execute = DbUtil.execute(sql1);
            System.out.println("execute:"+execute);
            int execute2 = DbUtil.execute(sql2);
            System.out.println("execute2:"+execute2);
            Entity query = DbUtil.query(sql3);
            System.out.println(StrUtil.format("status1:{},status2:{},error:{}\n\n\n"
                    ,query.getStr("Slave_IO_Running"),query.getStr("Slave_SQL_Running"),query.getStr("Last_Error")));
        }
    }

    private static void jxTbGw() {
        String sql1 = "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;";
        String sql2 = "START SLAVE;";
        String sql3 = "SHOW SLAVE STATUS;";
        DbUtil.setJdbcConfig(JdbcConfig.xy_gw());
        for (int i = 0; i < 300; i++) {
            int execute = DbUtil.execute(sql1);
            System.out.println("execute:"+execute);
            int execute2 = DbUtil.execute(sql2);
            System.out.println("execute2:"+execute2);
            Entity query = DbUtil.query(sql3);
            System.out.println(StrUtil.format("status1:{},status2:{},error:{}\n\n\n"
                    ,query.getStr("Slave_IO_Running"),query.getStr("Slave_SQL_Running"),query.getStr("Last_Error")));
        }
    }

    private static void extracted() {
        //查找
        String sql = "SELECT \n" +
                "    table_name tableName, \n" +
                "    column_name columnName\n" +
                "FROM \n" +
                "    information_schema.columns\n" +
                "WHERE \n" +
                "    table_schema = 'overseas_shop_testid'\n" +
                "    AND data_type = 'varchar'\n" +
                "    AND collation_name = 'utf8mb4_0900_ai_ci';";
        DbUtil.setJdbcConfig(JdbcConfig.xy_overseas_shop_testid());
        List<Entity> querys = DbUtil.querys(sql);
        StringBuilder builder = StrUtil.builder();
        for (Entity query : querys) {
            builder.append(StrUtil.format("ALTER TABLE {}\n" +
                    "MODIFY COLUMN {} VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\n",query.getStr("tableName"),query.getStr("columnName")));
        }
        System.out.println(builder);
    }

    /**
     * 查找修改的表字段
     */
    private static void find() {
        String data = "supplier_2_compensation_amount_fill_in=staff_account_id\n" +
                "role=role_id\n" +
                "purchase_sales_inventory_order=staff_account_id\n" +
                "purchase_sales_inventory_actual_sales_data=staff_account_id\n" +
                "car_model_country=car_model_id\n" +
                "claim_faulty_parts=claim_statement_id\n" +
                "notification=data_id\n" +
                "responsibility_part=claim_statement_id\n" +
                "claim_statement_audit=claim_statement_id\n" +
                "customer_permission=customer_id\n" +
                "data_manage_order_car_model_param=data_manage_order_id\n" +
                "data_manage_order_payment_param=data_manage_order_id\n" +
                "order_plan_custom_param=staff_account_country_or_region_id\n" +
                "income_statement_custom_param=staff_account_country_or_region_id\n" +
                "expense_payment_schedule_custom_param=staff_account_country_or_region_id\n" +
                "capital_plan_custom_param=staff_account_country_or_region_id\n" +
                "cash_flow_custom_param=staff_account_country_or_region_id\n" +
                "income_statement_custom_param=data_manage_car_model_id\n" +
                "expense_payment_schedule_custom_param=data_manage_car_model_id\n" +
                "part_system=part_album_id\n" +
                "part_pic_group=part_album_id\n" +
                "part_pic_group_pic_doc=part_album_id\n" +
                "part_pic_group_part_data=part_album_id\n" +
                "part_album_country=part_album_id\n" +
                "part_pic_group_pic_doc=part_pic_group_id\n" +
                "part_pic_group_part_data=part_system_id\n" +
                "part_pic_group_pic_doc=part_system_id\n" +
                "part_pic_group_part_data=part_pic_group_id\n" +
                "part_pic_group=part_system_id\n" +
                "part_pic_group_part_data=part_pic_group_pic_doc_id\n" +
                "part_data_pic=upload_file_id\n" +
                "part_order_audit=part_order_id\n" +
                "part_order_transportation=part_order_id\n" +
                "role_permission=role_id\n" +
                "staff_country=staff_account_id";
        String[] split = data.split("\n");
        StringBuilder builder = StrUtil.builder();
        for (String str : split) {
            String[] splData = str.split("=");
            builder.append(StrUtil.format("ALTER TABLE {}\n" +
                    "MODIFY {} VARCHAR(50);\n\n",splData[0],splData[1]));
        }
        System.out.println(builder);
    }

    /**
     * 查找自增的表格
     */
    private static void findAutoTable() {
        //查找自增的表
        String sql = "SELECT \n" +
                "    TABLE_NAME tableName, \n" +
                "    COLUMN_NAME columnName \n" +
                "FROM \n" +
                "    INFORMATION_SCHEMA.COLUMNS \n" +
                "WHERE \n" +
                "    TABLE_SCHEMA = 'overseas_shop' -- 替换为你的数据库名\n" +
                "    AND COLUMN_KEY = 'PRI'             -- 筛选主键\n" +
                "    AND EXTRA LIKE '%auto_increment%' -- 筛选自增属性\n" +
                "order by TABLE_NAME";
        DbUtil.setJdbcConfig(JdbcConfig.xy_overseas_shop());
        List<Entity> querys = DbUtil.querys(sql);
        StringBuilder builder = StrUtil.builder();
        for (Entity query : querys) {
            builder.append(StrUtil.format("ALTER TABLE {}\n" +
                    "MODIFY {} VARCHAR(50);\n\n",query.getStr("tableName"),query.getStr("columnName")));
        }
        System.out.println(builder);
    }
}
